﻿using System;
using System.Data;
using System.Data.SqlClient;
using AccessControl.Common;


namespace AccessControl.DataAcess
{
    public class PersonEventAccess
    {

        public static AccessControlDS.tblPersonEventDataTable SelectAll()
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdSelect = new SqlCommand("SELECT * FROM tblPersonEvent", cn);
                var tblpersonEvent = new AccessControlDS.tblPersonEventDataTable();
                try
                {
                    cn.Open();
                    var reader = cmdSelect.ExecuteReader();
                    while (reader.Read())
                    {
                        var newRow = tblpersonEvent.NewtblPersonEventRow();
                        newRow.fkPerson = (string)reader["fkPerson"];
                        newRow.fkEvent = (int)reader["fkEvent"];
                        newRow.IsAllowed = (bool)reader["IsAllowed"];
                        newRow.AccessProcedure = (byte)reader["AccessProcedure"];
                        newRow.AccessReason = Convert.ToString(reader["AccessReason"]);
                        if (reader["AccessStart"] != DBNull.Value) newRow.AccessStart = (DateTime)reader["AccessStart"];
                        if (reader["AccessEnd"] != DBNull.Value) newRow.AccessEnd = (DateTime)reader["AccessEnd"];

                        tblpersonEvent.AddtblPersonEventRow(newRow);
                    }
                }
                catch (Exception ex)
                {
                    var a = ex;
                }
                finally
                {
                    cn.Close();
                }
                return tblpersonEvent.Rows.Count != 0 ? tblpersonEvent : null;
            }
        }

        public static AccessControlDS.tblPersonEventDataTable SelectByFilter(string filter)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {

                var cmdSelect = new SqlCommand(String.Format("SELECT * FROM tblPersonEvent WHERE {0}", filter), cn);
                var tblpersonevent = new AccessControlDS.tblPersonEventDataTable();
                try
                {
                    cn.Open();
                    var reader = cmdSelect.ExecuteReader();
                    while (reader.Read())
                    {
                        var newRow = tblpersonevent.NewtblPersonEventRow();
                        newRow.fkPerson = (string)reader["fkPerson"];
                        newRow.fkEvent = (int)reader["fkEvent"];
                        newRow.IsAllowed = (bool)reader["IsAllowed"];
                        newRow.AccessProcedure = (byte)reader["AccessProcedure"];
                        newRow.AccessReason = Convert.ToString(reader["AccessReason"]);
                        if (reader["AccessStart"] != DBNull.Value)
                            newRow.AccessStart = (DateTime)reader["AccessStart"];
                        if (reader["AccessEnd"] != DBNull.Value)
                            newRow.AccessEnd = (DateTime)reader["AccessEnd"];

                        tblpersonevent.AddtblPersonEventRow(newRow);
                    }
                }
                catch (Exception ex)
                {
                    var a = ex;
                }
                finally
                {
                    cn.Close();
                }
                return tblpersonevent.Rows.Count != 0 ? tblpersonevent : null;
            }
        }

        public static void Insert(AccessControlDS.tblPersonEventRow row)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdInsert = new SqlCommand("spPersonEvent_Insert", cn) { CommandType = CommandType.StoredProcedure };
                cmdInsert.Parameters.AddWithValue("@fkPerson", row.fkPerson);
                cmdInsert.Parameters.AddWithValue("@fkEvent", row.fkEvent);
                cmdInsert.Parameters.AddWithValue("@AccessProcedure", row.AccessProcedure);
                cmdInsert.Parameters.AddWithValue("@IsAllowed", row.IsAllowed);
                cmdInsert.Parameters.AddWithValue("@AccessReason", row.AccessReason);
                cmdInsert.Parameters.AddWithValue("@AccessStart", row.AccessStart);
                cmdInsert.Parameters.AddWithValue("@AccessEnd", row.AccessEnd);
                try
                {
                    cn.Open();
                    cmdInsert.ExecuteNonQuery();
                }
                finally
                {
                    cn.Close();
                }
            }
        }

        public static void Update(AccessControlDS.tblPersonEventRow row)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdUpdate = new SqlCommand("spPersonEvent_Update", cn) { CommandType = CommandType.StoredProcedure };
                cmdUpdate.Parameters.AddWithValue("@fkPerson", row.fkPerson);
                cmdUpdate.Parameters.AddWithValue("@fkEvent", row.fkEvent);
                cmdUpdate.Parameters.AddWithValue("@AccessProcedure", row.AccessProcedure);
                cmdUpdate.Parameters.AddWithValue("@IsAllowed", row.IsAllowed);
                cmdUpdate.Parameters.AddWithValue("@AccessReason", row.AccessReason);
                cmdUpdate.Parameters.AddWithValue("@AccessStart", row.AccessStart);
                cmdUpdate.Parameters.AddWithValue("@AccessEnd", row.AccessEnd);
                try
                {
                    cn.Open();
                    cmdUpdate.ExecuteNonQuery();
                }
                finally
                {
                    cn.Close();
                }
            }
        }

        public static void Delete(AccessControlDS.tblPersonEventRow row)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdDelete = new SqlCommand("spPersonEvent_Delete", cn) { CommandType = CommandType.StoredProcedure };
                cmdDelete.Parameters.AddWithValue("@fkEvent", row.fkEvent);
                cmdDelete.Parameters.AddWithValue("@fkPerson", row.fkPerson);
                try
                {
                    cn.Open();
                    cmdDelete.ExecuteNonQuery();
                }
                finally
                {
                    cn.Close();
                }
            }
        }

    }
}
